Example Database

Let's explore the database example used in this course.

We'll cover the following

Data Definition Language for the database
Entity-Relationship Diagram for the database

Most of the topics in this course are illustrated using a database for a hypothetical bug-tracking application. The DDL and the ERD for the mentioned application are given below.

Data Definition Language for the database#

The following Data Definition Language shows how tables are defined. In some cases, choices are made for the sake of examples later in the course, so they might not always be the choices one would make in a real-world application. Mostly, only standard SQL is used, so the example is applicable to any brand of the database. Some MySQL data types also appear, such as SERIAL and BIGINT. The DDL for the database can be seen below. We can also copy and/or download the code.

DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS BugStatus;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Comments;
DROP TABLE IF EXISTS Screenshots;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS BugsProducts;
CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);
CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);
CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,

Enter to Rename, Shift+Enter to Preview

Data Definition Language for the database

Entity-Relationship Diagram for the database#

The Entity-Relationship Diagram for this database is shown in the figure given below. Notice the three connections between the Bugs table and the Accounts table, representing three separate foreign keys.

In some chapters, especially those on Logical Antipatterns, different database definitions are shown, either to exhibit the antipattern or to show an alternative solution that avoids the antipattern.

Conventions

Synopsis: Jaywalking

Mark as Completed

Report an Issue

Introduction

Logical Antipattern - Jaywalking

Logical Antipattern - Naive Trees

Logical Antipattern - ID Required

Logical Antipattern - Keyless Entry

Assessment: Logical Antipatterns - Part 1

Logical Antipattern - Entity-Attribute-Value

Logical Antipattern - Polymorphic Associations

Logical Antipattern - Multicolumn Attributes

Logical Antipattern - Metadata Tribbles

Assessment: Logical Antipatterns - Part 2

Physical Antipattern - Rounding Errors

Physical Antipattern - 31 Flavors

Physical Antipattern - Phantom Files

Physical Antipattern - Index Shotgun

Assessment: Physical Antipatterns

Query Antipattern - Fear of the Unknown

Query Antipattern - Ambiguous Groups

Query Antipattern - Random Selection

Query Antipattern - Poor Man’s Search Engine

Query Antipattern - Spaghetti Query

Query Antipattern - Implicit Columns

Assessment: Query Antipattern

Application Development Antipattern - Readable Passwords

Application Development Antipattern - SQL Injection

Application Development Antipattern - Pseudokey Neat-Freak

Application Development Antipattern - See No Evil

Application Development Antipattern - Diplomatic Immunity

Application Development Antipattern - Magic Beans

Assessment: Application Development Antipattern

Conclusion

Example Database

Data Definition Language for the database#

Entity-Relationship Diagram for the database#